package advertising.sql

//  .--,       .--,
// ( (  \.---./  ) )
//  '.__/o   o\__.'
//     {=  ^  =}
//      >  -  <
//     /       \
//    //       \\
//   //|   .   |\\
//   "'\       /'"_.-~^`'-.
//      \  _  /--'         `
//    ___)( )(___
//   (((__) (__)))    高山仰止, 景行行止; 虽不能至, 心向往之.
//                    High mountains, scenery. Although not to, the heart yearns for it.

object BaseKpiSQL {

  def geoClickedCount2Hive =
    """
      |insert into table ads.geo_click_count_collect
      |partition(click_date)
      |select provinceName, cityName, count(1) number, click_date
      |from (select provinceName, cityName, to_date(callbackDate) click_date from ad_raw_data) ad
      |group by provinceName, cityName, click_date
    """.stripMargin

  def geoTabDataCollect2Hive =
    """
      |insert into table ads.geo_tab_data_collect
      |partition(markDate)
      |select
      |	provinceName,
      |	cityName,
      |	sum(requestNum) requestCount,
      |	sum(validRequestNum) validRequestCount,
      |	sum(adRequestNum) adRequestCount,
      |	sum(biddingNum) biddingCount,
      |	sum(succeedBiddingNum) succeedBiddingCount,
      |	sum(showedNum) showedCount,
      |	sum(clickedNum) clickedCount,
      |	sum(adCost) adCostCount,
      |	sum(adConsume) adConsumeCount,
      | markDate
      |from base_tab_data
      |group by provinceName, cityName, markDate
    """.stripMargin

  def operatorTabDataCollect2Hive =
    """
      |insert into table ads.operator_tab_data_collect
      |partition(markDate)
      |select
      |	ispName,
      |	sum(requestNum) requestCount,
      |	sum(validRequestNum) validRequestCount,
      |	sum(adRequestNum) adRequestCount,
      |	sum(biddingNum) biddingCount,
      |	sum(succeedBiddingNum) succeedBiddingCount,
      |	sum(showedNum) showedCount,
      |	sum(clickedNum) clickedCount,
      |	sum(adCost) adCostCount,
      |	sum(adConsume) adConsumeCount,
      | markDate
      |from base_tab_data
      |group by ispName, markDate
    """.stripMargin

  def networkTypeTabDataCollect2Hive =
    """
      |insert into table ads.network_type_tab_data_collect
      |partition(markDate)
      |select
      |	networkMannerName,
      |	sum(requestNum) requestCount,
      |	sum(validRequestNum) validRequestCount,
      |	sum(adRequestNum) adRequestCount,
      |	sum(biddingNum) biddingCount,
      |	sum(succeedBiddingNum) succeedBiddingCount,
      |	sum(showedNum) showedCount,
      |	sum(clickedNum) clickedCount,
      |	sum(adCost) adCostCount,
      |	sum(adConsume) adConsumeCount,
      | markDate
      |from base_tab_data
      |group by networkMannerName, markDate
    """.stripMargin

  def deviceTypeTabDataCollect2Hive =
    """
      |insert into table ads.device_type_tab_data_collect
      |partition(markDate)
      |select
      |	deviceName,
      |	sum(requestNum) requestCount,
      |	sum(validRequestNum) validRequestCount,
      |	sum(adRequestNum) adRequestCount,
      |	sum(biddingNum) biddingCount,
      |	sum(succeedBiddingNum) succeedBiddingCount,
      |	sum(showedNum) showedCount,
      |	sum(clickedNum) clickedCount,
      |	sum(adCost) adCostCount,
      |	sum(adConsume) adConsumeCount,
      | markDate
      |from base_tab_data
      |group by deviceName, markDate
    """.stripMargin

  def osTypeTabDataCollect2Hive =
    """
      |insert into table ads.os_type_tab_data_collect
      |partition(markDate)
      |select
      |	osName,
      |	sum(requestNum) requestCount,
      |	sum(validRequestNum) validRequestCount,
      |	sum(adRequestNum) adRequestCount,
      |	sum(biddingNum) biddingCount,
      |	sum(succeedBiddingNum) succeedBiddingCount,
      |	sum(showedNum) showedCount,
      |	sum(clickedNum) clickedCount,
      |	sum(adCost) adCostCount,
      |	sum(adConsume) adConsumeCount,
      | markDate
      |from base_tab_data
      |group by osName, markDate
    """.stripMargin

  def appNameTabDataCollect2Hive =
    """
      |insert into table ads.app_name_tab_data_collect
      |partition(markDate)
      |select
      |	appName,
      |	sum(requestNum) requestCount,
      |	sum(validRequestNum) validRequestCount,
      |	sum(adRequestNum) adRequestCount,
      |	sum(biddingNum) biddingCount,
      |	sum(succeedBiddingNum) succeedBiddingCount,
      |	sum(showedNum) showedCount,
      |	sum(clickedNum) clickedCount,
      |	sum(adCost) adCostCount,
      |	sum(adConsume) adConsumeCount,
      | markDate
      |from base_tab_data
      |group by appName, markDate
    """.stripMargin
}
